﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class SalesOrderDA : ISalesOrderDA
    {
        private string ACTUAL_SHIP_DATE = "@ACTUAL_SHIP_DATE";
        private string B_ADDRESS = "@B_ADDRESS";
        private string B_CITY = "@B_CITY";
        private string B_COMPANY = "@B_COMPANY";
        private string B_CONTACT1 = "@B_CONTACT1";
        private string B_CONTACT2 = "@B_CONTACT2";
        private string B_COUNTRY = "@B_COUNTRY";
        private string B_COUNTRY_CODE = "@B_COUNTRY_CODE";
        private string B_EMAIL1 = "@B_EMAIL1";
        private string B_EMAIL2 = "@B_EMAIL2";
        private string B_FAX1 = "@B_FAX1";
        private string B_FAX2 = "@B_FAX2";
        private string B_PHONE1 = "@B_PHONE1";
        private string B_PHONE2 = "@B_PHONE2";
        private string B_STATE = "@B_STATE";
        private string B_VAT = "@B_VAT";
        private string B_ZIP = "@B_ZIP";
        private string BILL_TO_ID = "@BILL_TO_ID";
        private string C_ADDRESS = "@C_ADDRESS";
        private string C_CITY = "@C_CITY";
        private string C_COMPANY = "@C_COMPANY";
        private string C_CONTACT1 = "@C_CONTACT1";
        private string C_CONTACT2 = "@C_CONTACT2";
        private string C_COUNTRY = "@C_COUNTRY";
        private string C_COUNTRY_CODE = "@C_COUNTRY_CODE";
        private string C_EMAIL1 = "@C_EMAIL1";
        private string C_EMAIL2 = "@C_EMAIL2";
        private string C_FAX1 = "@C_FAX1";
        private string C_FAX2 = "@C_FAX2";
        private string C_PHONE1 = "@C_PHONE1";
        private string C_PHONE2 = "@C_PHONE2";
        private string C_PROV = "@C_PROV";
        private string C_VAT = "@C_VAT";
        private string C_ZIP = "@C_ZIP";
        private string CARRIER_ADDRESS = "@CARRIER_ADDRESS";
        private string CARRIER_CITY = "@CARRIER_CITY";
        private string CARRIER_COUNTRY = "@CARRIER_COUNTRY";
        private string CARRIER_COUNTRY_CODE = "@CARRIER_COUNTRY_CODE";
        private string CARRIER_ID = "@CARRIER_ID";
        private string CARRIER_NAME = "@CARRIER_NAME";
        private string CARRIER_PHONE = "@CARRIER_PHONE";
        private string CARRIER_PROV = "@CARRIER_PROV";
        private string CARRIER_ZIP = "@CARRIER_ZIP";
        private string CONSIGNEE_ID = "@CONSIGNEE_ID";
        private string CONTAINER_QTY = "@CONTAINER_QTY";
        private string CONTAINER_TYPE = "@CONTAINER_TYPE";
        private string COUNTRY_DESTINATION = "@COUNTRY_DESTINATION";
        private string COUNTRY_OF_ORIGIN = "@COUNTRY_OF_ORIGIN";
        private string CREATED_BY = "@CREATED_BY";
        private string DELIVERY_DATE = "@DELIVERY_DATE";
        private string DELIVERY_DATE2 = "@DELIVERY_DATE2";
        private string DELIVERY_PLACE = "@DELIVERY_PLACE";
        private string DEPARTURE_DATE = "@DEPARTURE_DATE";
        private string DISCHARGE_PLACE = "@DISCHARGE_PLACE";
        private string DOOR = "@DOOR";
        private string DRIVER_NAME = "@DRIVER_NAME";
        private string EXTERN_ORDER_ID = "@EXTERN_ORDER_ID";
        private string EXTERNAL_LOAD_ID = "@EXTERNAL_LOAD_ID";
        private string INCO_TERMS = "@INCO_TERMS";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string LOAD_ID = "@LOAD_ID";
        private string OPEN_QTY = "@OPEN_QTY";
        private string ORDER_DATE = "@ORDER_DATE";
        private string ORDER_ID = "@ORDER_ID";
        private string OWNER_ID = "@OWNER_ID";
        private string PAYMENT_TERM = "@PAYMENT_TERM";
        private string PRIORITY = "@PRIORITY";
        private string PRO_NUMBER = "@PRO_NUMBER";
        private string REMARK = "@REMARK";
        private string REMARK2 = "@REMARK2";
        private string REQUESTED_DELIVERY_DATE = "@REQUESTED_DELIVERY_DATE";
        private string ROUTE = "@ROUTE";
        private string SQL_CHECK_SALES_ORDER_ID_UNIQUE = " SELECT COUNT(1) FROM @_@SALES_ORDER WHERE ORDER_ID = @ORDER_ID ";
        private string SQL_DELETE_SALES_ORDER = " DELETE FROM @_@SALES_ORDER WHERE ORDER_ID = @ORDER_ID ";
        private string SQL_INSERT_SALES_ORDER = " INSERT INTO @_@SALES_ORDER ( WH_ID, ORDER_ID, OWNER_ID, ORDER_DATE, REQUESTED_DELIVERY_DATE, ACTUAL_SHIP_DATE, DELIVERY_DATE, DELIVERY_DATE2, PRIORITY, TYPE, OPEN_QTY, STATUS, EXTERN_ORDER_ID, CONSIGNEE_ID, C_COMPANY, C_ADDRESS, C_CITY, C_PROV, C_ZIP, C_COUNTRY_CODE, C_COUNTRY, C_VAT, C_CONTACT1, C_CONTACT2, C_EMAIL1, C_EMAIL2, C_PHONE1, C_PHONE2, C_FAX1, C_FAX2, BILL_TO_ID, B_COMPANY, B_ADDRESS, B_CITY, B_STATE, B_ZIP, B_COUNTRY_CODE, B_COUNTRY, B_CONTACT1, B_CONTACT2, B_EMAIL1, B_EMAIL2, B_PHONE1, B_PHONE2, B_FAX1, B_FAX2, B_VAT, CARRIER_ID, CARRIER_NAME, CARRIER_ADDRESS, CARRIER_CITY, CARRIER_PROV, CARRIER_ZIP, CARRIER_COUNTRY_CODE, CARRIER_COUNTRY, CARRIER_PHONE, DEPARTURE_DATE, TRAILER_NUMBER, TRAILER_OWNER, DRIVER_NAME, PRO_NUMBER, DOOR, ROUTE, STOP, STAGE, TRANSPORTATION_MODE, TRANSPORTATION_SERVICE, INCO_TERMS, PAYMENT_TERM, CONTAINER_TYPE, CONTAINER_QTY, LOAD_ID, EXTERNAL_LOAD_ID, COUNTRY_OF_ORIGIN, COUNTRY_DESTINATION, DISCHARGE_PLACE, DELIVERY_PLACE, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, REMARK2, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @ORDER_ID, @OWNER_ID, @ORDER_DATE, @REQUESTED_DELIVERY_DATE, @ACTUAL_SHIP_DATE, @DELIVERY_DATE, @DELIVERY_DATE2, @PRIORITY, @TYPE, @OPEN_QTY, @STATUS, @EXTERN_ORDER_ID, @CONSIGNEE_ID, @C_COMPANY, @C_ADDRESS, @C_CITY, @C_PROV, @C_ZIP, @C_COUNTRY_CODE, @C_COUNTRY, @C_VAT, @C_CONTACT1, @C_CONTACT2, @C_EMAIL1, @C_EMAIL2, @C_PHONE1, @C_PHONE2, @C_FAX1, @C_FAX2, @BILL_TO_ID, @B_COMPANY, @B_ADDRESS, @B_CITY, @B_STATE, @B_ZIP, @B_COUNTRY_CODE, @B_COUNTRY, @B_CONTACT1, @B_CONTACT2, @B_EMAIL1, @B_EMAIL2, @B_PHONE1, @B_PHONE2, @B_FAX1, @B_FAX2, @B_VAT, @CARRIER_ID, @CARRIER_NAME, @CARRIER_ADDRESS, @CARRIER_CITY, @CARRIER_PROV, @CARRIER_ZIP, @CARRIER_COUNTRY_CODE, @CARRIER_COUNTRY, @CARRIER_PHONE, @DEPARTURE_DATE, @TRAILER_NUMBER, @TRAILER_OWNER, @DRIVER_NAME, @PRO_NUMBER, @DOOR, @ROUTE, @STOP, @STAGE, @TRANSPORTATION_MODE, @TRANSPORTATION_SERVICE, @INCO_TERMS, @PAYMENT_TERM, @CONTAINER_TYPE, @CONTAINER_QTY, @LOAD_ID, @EXTERNAL_LOAD_ID, @COUNTRY_OF_ORIGIN, @COUNTRY_DESTINATION, @DISCHARGE_PLACE, @DELIVERY_PLACE, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @REMARK, @REMARK2, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_SALES_ORDER = " SELECT  SO.ROWID AS ROWID, SO.WH_ID AS WH_ID, SO.ORDER_ID AS ORDER_ID, SO.OWNER_ID AS OWNER_ID, SO.ORDER_DATE AS ORDER_DATE, SO.REQUESTED_DELIVERY_DATE AS REQUESTED_DELIVERY_DATE, SO.ACTUAL_SHIP_DATE AS ACTUAL_SHIP_DATE, SO.DELIVERY_DATE AS DELIVERY_DATE, SO.DELIVERY_DATE2 AS DELIVERY_DATE2, SO.PRIORITY AS PRIORITY, SO.TYPE AS TYPE, SO.OPEN_QTY AS OPEN_QTY, SO.STATUS AS STATUS, T.[TEXT] AS STATUS_D,T2.[TEXT] AS TYPE_D,SO.EXTERN_ORDER_ID AS EXTERN_ORDER_ID, SO.CONSIGNEE_ID AS CONSIGNEE_ID, SO.C_COMPANY AS C_COMPANY, SO.C_ADDRESS AS C_ADDRESS, SO.C_CITY AS C_CITY, SO.C_PROV AS C_PROV, SO.C_ZIP AS C_ZIP, SO.C_COUNTRY_CODE AS C_COUNTRY_CODE, SO.C_COUNTRY AS C_COUNTRY, SO.C_VAT AS C_VAT, SO.C_CONTACT1 AS C_CONTACT1, SO.C_CONTACT2 AS C_CONTACT2, SO.C_EMAIL1 AS C_EMAIL1, SO.C_EMAIL2 AS C_EMAIL2, SO.C_PHONE1 AS C_PHONE1, SO.C_PHONE2 AS C_PHONE2, SO.C_FAX1 AS C_FAX1, SO.C_FAX2 AS C_FAX2, SO.BILL_TO_ID AS BILL_TO_ID, SO.B_COMPANY AS B_COMPANY, SO.B_ADDRESS AS B_ADDRESS, SO.B_CITY AS B_CITY, SO.B_STATE AS B_STATE, SO.B_ZIP AS B_ZIP, SO.B_COUNTRY_CODE AS B_COUNTRY_CODE, SO.B_COUNTRY AS B_COUNTRY, SO.B_CONTACT1 AS B_CONTACT1, SO.B_CONTACT2 AS B_CONTACT2, SO.B_EMAIL1 AS B_EMAIL1, SO.B_EMAIL2 AS B_EMAIL2, SO.B_PHONE1 AS B_PHONE1, SO.B_PHONE2 AS B_PHONE2, SO.B_FAX1 AS B_FAX1, SO.B_FAX2 AS B_FAX2, SO.B_VAT AS B_VAT, SO.CARRIER_ID AS CARRIER_ID, SO.CARRIER_NAME AS CARRIER_NAME, SO.CARRIER_ADDRESS AS CARRIER_ADDRESS, SO.CARRIER_CITY AS CARRIER_CITY, SO.CARRIER_PROV AS CARRIER_PROV, SO.CARRIER_ZIP AS CARRIER_ZIP, SO.CARRIER_COUNTRY_CODE AS CARRIER_COUNTRY_CODE, SO.CARRIER_COUNTRY AS CARRIER_COUNTRY, SO.CARRIER_PHONE AS CARRIER_PHONE, SO.DEPARTURE_DATE AS DEPARTURE_DATE, SO.TRAILER_NUMBER AS TRAILER_NUMBER, SO.TRAILER_OWNER AS TRAILER_OWNER, SO.DRIVER_NAME AS DRIVER_NAME, SO.PRO_NUMBER AS PRO_NUMBER, SO.DOOR AS DOOR, SO.ROUTE AS ROUTE, SO.STOP AS STOP, SO.STAGE AS STAGE, SO.TRANSPORTATION_MODE AS TRANSPORTATION_MODE, SO.TRANSPORTATION_SERVICE AS TRANSPORTATION_SERVICE, SO.INCO_TERMS AS INCO_TERMS, SO.PAYMENT_TERM AS PAYMENT_TERM, SO.CONTAINER_TYPE AS CONTAINER_TYPE, SO.CONTAINER_QTY AS CONTAINER_QTY, SO.LOAD_ID AS LOAD_ID, SO.EXTERNAL_LOAD_ID AS EXTERNAL_LOAD_ID, SO.COUNTRY_OF_ORIGIN AS COUNTRY_OF_ORIGIN, SO.COUNTRY_DESTINATION AS COUNTRY_DESTINATION, SO.DISCHARGE_PLACE AS DISCHARGE_PLACE, SO.DELIVERY_PLACE AS DELIVERY_PLACE, SO.UDF1 AS UDF1, SO.UDF2 AS UDF2, SO.UDF3 AS UDF3, SO.UDF4 AS UDF4, SO.UDF5 AS UDF5, SO.UDF6 AS UDF6, SO.UDF7 AS UDF7, SO.UDF8 AS UDF8, SO.UDF9 AS UDF9, SO.UDF10 AS UDF10, SO.REMARK AS REMARK, SO.REMARK2 AS REMARK2, SO.IS_ACTIVE AS IS_ACTIVE, SO.TS AS TS, SO.CREATED_BY AS CREATED_BY, SO.CREATED_DATE AS CREATED_DATE, SO.UPDATED_BY AS UPDATED_BY, SO.UPDATED_DATE AS UPDATED_DATE, SO.SUBMITTED_DATE,SO.SUBMITTED_BY  FROM  @_@SALES_ORDER SO  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = SO.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = SO.TYPE AND SCD2.SYS_CODE_TYPE = 'SalesOrderType' LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+SO.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T2 ON 'SalesOrderType.'+SO.TYPE = T2.ITEM_ID  \t\t\tAND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON SO.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID  INNER JOIN SYSADM.USER_CUSTOMER UC ON SO.CONSIGNEE_ID = UC.CUSTOMER_ID AND UC.[USER_ID] = @USER_ID ";
        private string SQL_UPDATE_SALES_ORDER = " UPDATE @_@SALES_ORDER SET WH_ID = @WH_ID, OWNER_ID = @OWNER_ID, ORDER_DATE = @ORDER_DATE, REQUESTED_DELIVERY_DATE = @REQUESTED_DELIVERY_DATE, ACTUAL_SHIP_DATE = @ACTUAL_SHIP_DATE, DELIVERY_DATE = @DELIVERY_DATE, DELIVERY_DATE2 = @DELIVERY_DATE2, PRIORITY = @PRIORITY, TYPE = @TYPE, OPEN_QTY = @OPEN_QTY, STATUS = @STATUS, EXTERN_ORDER_ID = @EXTERN_ORDER_ID, CONSIGNEE_ID = @CONSIGNEE_ID, C_COMPANY = @C_COMPANY, C_ADDRESS = @C_ADDRESS, C_CITY = @C_CITY, C_PROV = @C_PROV, C_ZIP = @C_ZIP, C_COUNTRY_CODE = @C_COUNTRY_CODE, C_COUNTRY = @C_COUNTRY, C_VAT = @C_VAT, C_CONTACT1 = @C_CONTACT1, C_CONTACT2 = @C_CONTACT2, C_EMAIL1 = @C_EMAIL1, C_EMAIL2 = @C_EMAIL2, C_PHONE1 = @C_PHONE1, C_PHONE2 = @C_PHONE2, C_FAX1 = @C_FAX1, C_FAX2 = @C_FAX2, BILL_TO_ID = @BILL_TO_ID, B_COMPANY = @B_COMPANY, B_ADDRESS = @B_ADDRESS, B_CITY = @B_CITY, B_STATE = @B_STATE, B_ZIP = @B_ZIP, B_COUNTRY_CODE = @B_COUNTRY_CODE, B_COUNTRY = @B_COUNTRY, B_CONTACT1 = @B_CONTACT1, B_CONTACT2 = @B_CONTACT2, B_EMAIL1 = @B_EMAIL1, B_EMAIL2 = @B_EMAIL2, B_PHONE1 = @B_PHONE1, B_PHONE2 = @B_PHONE2, B_FAX1 = @B_FAX1, B_FAX2 = @B_FAX2, B_VAT = @B_VAT, CARRIER_ID = @CARRIER_ID, CARRIER_NAME = @CARRIER_NAME, CARRIER_ADDRESS = @CARRIER_ADDRESS, CARRIER_CITY = @CARRIER_CITY, CARRIER_PROV = @CARRIER_PROV, CARRIER_ZIP = @CARRIER_ZIP, CARRIER_COUNTRY_CODE = @CARRIER_COUNTRY_CODE, CARRIER_COUNTRY = @CARRIER_COUNTRY, CARRIER_PHONE = @CARRIER_PHONE, DEPARTURE_DATE = @DEPARTURE_DATE, TRAILER_NUMBER = @TRAILER_NUMBER, TRAILER_OWNER = @TRAILER_OWNER, DRIVER_NAME = @DRIVER_NAME, PRO_NUMBER = @PRO_NUMBER, DOOR = @DOOR, ROUTE = @ROUTE, STOP = @STOP, STAGE = @STAGE, TRANSPORTATION_MODE = @TRANSPORTATION_MODE, TRANSPORTATION_SERVICE = @TRANSPORTATION_SERVICE, INCO_TERMS = @INCO_TERMS, PAYMENT_TERM = @PAYMENT_TERM, CONTAINER_TYPE = @CONTAINER_TYPE, CONTAINER_QTY = @CONTAINER_QTY, LOAD_ID = @LOAD_ID, EXTERNAL_LOAD_ID = @EXTERNAL_LOAD_ID, COUNTRY_OF_ORIGIN = @COUNTRY_OF_ORIGIN, COUNTRY_DESTINATION = @COUNTRY_DESTINATION, DISCHARGE_PLACE = @DISCHARGE_PLACE, DELIVERY_PLACE = @DELIVERY_PLACE, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REMARK = @REMARK, REMARK2 = @REMARK2, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID  ";
        private string SQL_UPDATE_SALES_ORDER_STATUS = " UPDATE @_@SALES_ORDER SET STATUS = @STATUS, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID  ";
        private string SQL_UPDATE_SALES_ORDER_SUBMITTEDBY = " UPDATE @_@SALES_ORDER SET  UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() , SUBMITTED_DATE=@SUBMITTED_DATE , SUBMITTED_BY=@SUBMITTED_BY  WHERE ORDER_ID = @ORDER_ID  ";
        private string STAGE = "@STAGE";
        private string STATUS = "@STATUS";
        private string STOP = "@STOP";
        private string TRAILER_NUMBER = "@TRAILER_NUMBER";
        private string TRAILER_OWNER = "@TRAILER_OWNER";
        private string TRANSPORTATION_MODE = "@TRANSPORTATION_MODE";
        private string TRANSPORTATION_SERVICE = "@TRANSPORTATION_SERVICE";
        private string TS = "@TS";
        private string TYPE = "@TYPE";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckSalesOrderIDUnique(string salesOrderID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", salesOrderID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_SALES_ORDER_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckSalesOrderIDUnique(DataBase dataBase, DbTransaction tran, string salesOrderID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", salesOrderID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_SALES_ORDER_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteSalesOrder(List<string> salesOrderID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int j = 0; j < salesOrderID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", salesOrderID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_SALES_ORDER.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteSalesOrder(DataBase dataBase, DbTransaction tran, List<string> salesOrderID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < salesOrderID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", salesOrderID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_SALES_ORDER.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllSalesOrder(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_SALES_ORDER.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllSalesOrder(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_SALES_ORDER.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public SalesOrderInfo GetSalesOrderByID(string salesorderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_SALES_ORDER.Replace("@_@", whLoginID) + " WHERE ORDER_ID = @ORDER_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", salesorderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            SalesOrderInfo salesOrderInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    salesOrderInfo = new SalesOrderInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), (reader["ORDER_DATE"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["REQUESTED_DELIVERY_DATE"].ToString() != "") ? DateTime.Parse(reader["REQUESTED_DELIVERY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["DELIVERY_DATE"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["DELIVERY_DATE2"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE2"].ToString()) : (CS00003 = new DateTime()), reader["PRIORITY"].ToString(), reader["TYPE"].ToString(), (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["CONSIGNEE_ID"].ToString(), reader["C_COMPANY"].ToString(), reader["C_ADDRESS"].ToString(), reader["C_CITY"].ToString(), reader["C_PROV"].ToString(), reader["C_ZIP"].ToString(), reader["C_COUNTRY_CODE"].ToString(), reader["C_COUNTRY"].ToString(), reader["C_VAT"].ToString(), reader["C_CONTACT1"].ToString(), reader["C_CONTACT2"].ToString(), reader["C_EMAIL1"].ToString(), reader["C_EMAIL2"].ToString(), reader["C_PHONE1"].ToString(), reader["C_PHONE2"].ToString(), reader["C_FAX1"].ToString(), reader["C_FAX2"].ToString(), reader["BILL_TO_ID"].ToString(), reader["B_COMPANY"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_VAT"].ToString(), reader["CARRIER_ID"].ToString(), reader["CARRIER_NAME"].ToString(), reader["CARRIER_ADDRESS"].ToString(), reader["CARRIER_CITY"].ToString(), reader["CARRIER_PROV"].ToString(), reader["CARRIER_ZIP"].ToString(), reader["CARRIER_COUNTRY_CODE"].ToString(), reader["CARRIER_COUNTRY"].ToString(), reader["CARRIER_PHONE"].ToString(), (reader["DEPARTURE_DATE"].ToString() != "") ? DateTime.Parse(reader["DEPARTURE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["TRAILER_NUMBER"].ToString(), reader["TRAILER_OWNER"].ToString(), reader["DRIVER_NAME"].ToString(), reader["PRO_NUMBER"].ToString(), reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["STOP"].ToString(), reader["STAGE"].ToString(), reader["TRANSPORTATION_MODE"].ToString(), reader["TRANSPORTATION_SERVICE"].ToString(), reader["INCO_TERMS"].ToString(), reader["PAYMENT_TERM"].ToString(), reader["CONTAINER_TYPE"].ToString(), (reader["CONTAINER_QTY"].ToString() != "") ? decimal.Parse(reader["CONTAINER_QTY"].ToString()) : 0M, reader["LOAD_ID"].ToString(), reader["EXTERNAL_LOAD_ID"].ToString(), reader["COUNTRY_OF_ORIGIN"].ToString(), reader["COUNTRY_DESTINATION"].ToString(), reader["DISCHARGE_PLACE"].ToString(), reader["DELIVERY_PLACE"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["REMARK2"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["SUBMITTED_BY"].ToString(), (reader["SUBMITTED_DATE"].ToString() != "") ? DateTime.Parse(reader["SUBMITTED_DATE"].ToString()) : new DateTime());
                }
            }
            return salesOrderInfo;
        }

        public SalesOrderInfo GetSalesOrderByID(DataBase dataBase, DbTransaction tran, string salesorderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_SALES_ORDER.Replace("@_@", whLoginID) + " WHERE ORDER_ID = @ORDER_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", salesorderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            SalesOrderInfo salesOrderInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                salesOrderInfo = new SalesOrderInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), (reader["ORDER_DATE"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["REQUESTED_DELIVERY_DATE"].ToString() != "") ? DateTime.Parse(reader["REQUESTED_DELIVERY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["DELIVERY_DATE"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["DELIVERY_DATE2"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE2"].ToString()) : (CS00003 = new DateTime()), reader["PRIORITY"].ToString(), reader["TYPE"].ToString(), (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["CONSIGNEE_ID"].ToString(), reader["C_COMPANY"].ToString(), reader["C_ADDRESS"].ToString(), reader["C_CITY"].ToString(), reader["C_PROV"].ToString(), reader["C_ZIP"].ToString(), reader["C_COUNTRY_CODE"].ToString(), reader["C_COUNTRY"].ToString(), reader["C_VAT"].ToString(), reader["C_CONTACT1"].ToString(), reader["C_CONTACT2"].ToString(), reader["C_EMAIL1"].ToString(), reader["C_EMAIL2"].ToString(), reader["C_PHONE1"].ToString(), reader["C_PHONE2"].ToString(), reader["C_FAX1"].ToString(), reader["C_FAX2"].ToString(), reader["BILL_TO_ID"].ToString(), reader["B_COMPANY"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_VAT"].ToString(), reader["CARRIER_ID"].ToString(), reader["CARRIER_NAME"].ToString(), reader["CARRIER_ADDRESS"].ToString(), reader["CARRIER_CITY"].ToString(), reader["CARRIER_PROV"].ToString(), reader["CARRIER_ZIP"].ToString(), reader["CARRIER_COUNTRY_CODE"].ToString(), reader["CARRIER_COUNTRY"].ToString(), reader["CARRIER_PHONE"].ToString(), (reader["DEPARTURE_DATE"].ToString() != "") ? DateTime.Parse(reader["DEPARTURE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["TRAILER_NUMBER"].ToString(), reader["TRAILER_OWNER"].ToString(), reader["DRIVER_NAME"].ToString(), reader["PRO_NUMBER"].ToString(), reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["STOP"].ToString(), reader["STAGE"].ToString(), reader["TRANSPORTATION_MODE"].ToString(), reader["TRANSPORTATION_SERVICE"].ToString(), reader["INCO_TERMS"].ToString(), reader["PAYMENT_TERM"].ToString(), reader["CONTAINER_TYPE"].ToString(), (reader["CONTAINER_QTY"].ToString() != "") ? decimal.Parse(reader["CONTAINER_QTY"].ToString()) : 0M, reader["LOAD_ID"].ToString(), reader["EXTERNAL_LOAD_ID"].ToString(), reader["COUNTRY_OF_ORIGIN"].ToString(), reader["COUNTRY_DESTINATION"].ToString(), reader["DISCHARGE_PLACE"].ToString(), reader["DELIVERY_PLACE"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["REMARK2"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["SUBMITTED_BY"].ToString(), (reader["SUBMITTED_DATE"].ToString() != "") ? DateTime.Parse(reader["SUBMITTED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return salesOrderInfo;
        }

        public DataSet GetSalesOrderByQueryList(List<string> sqlWhere, SalesOrderQueryEntity salesorderQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_SALES_ORDER.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!salesorderQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, salesorderQuery.CurrentPage, salesorderQuery.PageSize, salesorderQuery.SortField, salesorderQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetSalesOrderByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, SalesOrderQueryEntity salesorderQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_SALES_ORDER.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!salesorderQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, salesorderQuery.CurrentPage, salesorderQuery.PageSize, salesorderQuery.SortField, salesorderQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertSalesOrder(SalesOrderInfo salesOrderInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                salesOrderInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_SalesOrder_Parameters(salesOrderInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_SALES_ORDER.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertSalesOrder(DataBase dataBase, DbTransaction tran, SalesOrderInfo salesOrderInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                salesOrderInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_SalesOrder_Parameters(salesOrderInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_SALES_ORDER.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_SalesOrder_Parameters(SalesOrderInfo salesOrderInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.ORDER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 100), new SqlParameter(this.ORDER_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.REQUESTED_DELIVERY_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.ACTUAL_SHIP_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.DELIVERY_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.DELIVERY_DATE2, SqlDbType.DateTime, 8), new SqlParameter(this.PRIORITY, SqlDbType.VarChar, 20), new SqlParameter(this.TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.OPEN_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.EXTERN_ORDER_ID, SqlDbType.VarChar, 50), new SqlParameter(this.CONSIGNEE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.C_COMPANY, SqlDbType.VarChar, 50), new SqlParameter(this.C_ADDRESS, SqlDbType.VarChar, 200), 
                new SqlParameter(this.C_CITY, SqlDbType.VarChar, 50), new SqlParameter(this.C_PROV, SqlDbType.VarChar, 20), new SqlParameter(this.C_ZIP, SqlDbType.VarChar, 20), new SqlParameter(this.C_COUNTRY_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.C_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.C_VAT, SqlDbType.VarChar, 20), new SqlParameter(this.C_CONTACT1, SqlDbType.VarChar, 30), new SqlParameter(this.C_CONTACT2, SqlDbType.VarChar, 30), new SqlParameter(this.C_EMAIL1, SqlDbType.VarChar, 60), new SqlParameter(this.C_EMAIL2, SqlDbType.VarChar, 60), new SqlParameter(this.C_PHONE1, SqlDbType.VarChar, 20), new SqlParameter(this.C_PHONE2, SqlDbType.VarChar, 20), new SqlParameter(this.C_FAX1, SqlDbType.VarChar, 20), new SqlParameter(this.C_FAX2, SqlDbType.VarChar, 20), new SqlParameter(this.BILL_TO_ID, SqlDbType.VarChar, 20), new SqlParameter(this.B_COMPANY, SqlDbType.VarChar, 20), 
                new SqlParameter(this.B_ADDRESS, SqlDbType.VarChar, 200), new SqlParameter(this.B_CITY, SqlDbType.VarChar, 50), new SqlParameter(this.B_STATE, SqlDbType.VarChar, 20), new SqlParameter(this.B_ZIP, SqlDbType.VarChar, 20), new SqlParameter(this.B_COUNTRY_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.B_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.B_CONTACT1, SqlDbType.VarChar, 30), new SqlParameter(this.B_CONTACT2, SqlDbType.VarChar, 30), new SqlParameter(this.B_EMAIL1, SqlDbType.VarChar, 60), new SqlParameter(this.B_EMAIL2, SqlDbType.VarChar, 60), new SqlParameter(this.B_PHONE1, SqlDbType.VarChar, 20), new SqlParameter(this.B_PHONE2, SqlDbType.VarChar, 20), new SqlParameter(this.B_FAX1, SqlDbType.VarChar, 20), new SqlParameter(this.B_FAX2, SqlDbType.VarChar, 20), new SqlParameter(this.B_VAT, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_ID, SqlDbType.VarChar, 50), 
                new SqlParameter(this.CARRIER_NAME, SqlDbType.VarChar, 50), new SqlParameter(this.CARRIER_ADDRESS, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_CITY, SqlDbType.VarChar, 50), new SqlParameter(this.CARRIER_PROV, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_ZIP, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_COUNTRY_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.CARRIER_PHONE, SqlDbType.VarChar, 20), new SqlParameter(this.DEPARTURE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.TRAILER_NUMBER, SqlDbType.VarChar, 0x19), new SqlParameter(this.TRAILER_OWNER, SqlDbType.VarChar, 0x19), new SqlParameter(this.DRIVER_NAME, SqlDbType.VarChar, 0x19), new SqlParameter(this.PRO_NUMBER, SqlDbType.VarChar, 0x20), new SqlParameter(this.DOOR, SqlDbType.VarChar, 20), new SqlParameter(this.ROUTE, SqlDbType.VarChar, 20), new SqlParameter(this.STOP, SqlDbType.VarChar, 20), 
                new SqlParameter(this.STAGE, SqlDbType.VarChar, 20), new SqlParameter(this.TRANSPORTATION_MODE, SqlDbType.VarChar, 30), new SqlParameter(this.TRANSPORTATION_SERVICE, SqlDbType.VarChar, 30), new SqlParameter(this.INCO_TERMS, SqlDbType.VarChar, 20), new SqlParameter(this.PAYMENT_TERM, SqlDbType.VarChar, 20), new SqlParameter(this.CONTAINER_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.CONTAINER_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.LOAD_ID, SqlDbType.VarChar, 20), new SqlParameter(this.EXTERNAL_LOAD_ID, SqlDbType.VarChar, 20), new SqlParameter(this.COUNTRY_OF_ORIGIN, SqlDbType.VarChar, 30), new SqlParameter(this.COUNTRY_DESTINATION, SqlDbType.VarChar, 30), new SqlParameter(this.DISCHARGE_PLACE, SqlDbType.VarChar, 30), new SqlParameter(this.DELIVERY_PLACE, SqlDbType.VarChar, 30), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), 
                new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 0x200), new SqlParameter(this.REMARK2, SqlDbType.VarChar, 0x200), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(salesOrderInfo.WhID))
            {
                paramArray[0].Value = salesOrderInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = salesOrderInfo.OrderID;
            paramArray[2].Value = salesOrderInfo.OwnerID;
            paramArray[3].Value = salesOrderInfo.OrderDate;
            DateTime CS00003 = new DateTime();
            if (salesOrderInfo.RequestedDeliveryDate != CS00003)
            {
                paramArray[4].Value = salesOrderInfo.RequestedDeliveryDate;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            CS00003 = new DateTime();
            if (salesOrderInfo.ActualShipDate != CS00003)
            {
                paramArray[5].Value = salesOrderInfo.ActualShipDate;
            }
            else
            {
                paramArray[5].Value = DBNull.Value;
            }
            paramArray[6].Value = salesOrderInfo.DeliveryDate;
            if (salesOrderInfo.DeliveryDate2 != new DateTime())
            {
                paramArray[7].Value = salesOrderInfo.DeliveryDate2;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            paramArray[8].Value = salesOrderInfo.Priority;
            paramArray[9].Value = salesOrderInfo.Type;
            paramArray[10].Value = salesOrderInfo.OpenQty;
            paramArray[11].Value = salesOrderInfo.Status;
            paramArray[12].Value = salesOrderInfo.ExternOrderID;
            paramArray[13].Value = salesOrderInfo.ConsigneeID;
            if (!string.IsNullOrEmpty(salesOrderInfo.CCompany))
            {
                paramArray[14].Value = salesOrderInfo.CCompany;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CAddress))
            {
                paramArray[15].Value = salesOrderInfo.CAddress;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CCity))
            {
                paramArray[0x10].Value = salesOrderInfo.CCity;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CProv))
            {
                paramArray[0x11].Value = salesOrderInfo.CProv;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CZip))
            {
                paramArray[0x12].Value = salesOrderInfo.CZip;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CCountryCode))
            {
                paramArray[0x13].Value = salesOrderInfo.CCountryCode;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CCountry))
            {
                paramArray[20].Value = salesOrderInfo.CCountry;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CVat))
            {
                paramArray[0x15].Value = salesOrderInfo.CVat;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CContact1))
            {
                paramArray[0x16].Value = salesOrderInfo.CContact1;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CContact2))
            {
                paramArray[0x17].Value = salesOrderInfo.CContact2;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CEmail1))
            {
                paramArray[0x18].Value = salesOrderInfo.CEmail1;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CEmail2))
            {
                paramArray[0x19].Value = salesOrderInfo.CEmail2;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CPhone1))
            {
                paramArray[0x1a].Value = salesOrderInfo.CPhone1;
            }
            else
            {
                paramArray[0x1a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CPhone2))
            {
                paramArray[0x1b].Value = salesOrderInfo.CPhone2;
            }
            else
            {
                paramArray[0x1b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CFax1))
            {
                paramArray[0x1c].Value = salesOrderInfo.CFax1;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CFax2))
            {
                paramArray[0x1d].Value = salesOrderInfo.CFax2;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            paramArray[30].Value = salesOrderInfo.BillToID;
            if (!string.IsNullOrEmpty(salesOrderInfo.BCompany))
            {
                paramArray[0x1f].Value = salesOrderInfo.BCompany;
            }
            else
            {
                paramArray[0x1f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.BAddress))
            {
                paramArray[0x20].Value = salesOrderInfo.BAddress;
            }
            else
            {
                paramArray[0x20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.BCity))
            {
                paramArray[0x21].Value = salesOrderInfo.BCity;
            }
            else
            {
                paramArray[0x21].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.BState))
            {
                paramArray[0x22].Value = salesOrderInfo.BState;
            }
            else
            {
                paramArray[0x22].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.BZip))
            {
                paramArray[0x23].Value = salesOrderInfo.BZip;
            }
            else
            {
                paramArray[0x23].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.BCountryCode))
            {
                paramArray[0x24].Value = salesOrderInfo.BCountryCode;
            }
            else
            {
                paramArray[0x24].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.BCountry))
            {
                paramArray[0x25].Value = salesOrderInfo.BCountry;
            }
            else
            {
                paramArray[0x25].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.BContact1))
            {
                paramArray[0x26].Value = salesOrderInfo.BContact1;
            }
            else
            {
                paramArray[0x26].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.BContact2))
            {
                paramArray[0x27].Value = salesOrderInfo.BContact2;
            }
            else
            {
                paramArray[0x27].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.BEmail1))
            {
                paramArray[40].Value = salesOrderInfo.BEmail1;
            }
            else
            {
                paramArray[40].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.BEmail2))
            {
                paramArray[0x29].Value = salesOrderInfo.BEmail2;
            }
            else
            {
                paramArray[0x29].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.BPhone1))
            {
                paramArray[0x2a].Value = salesOrderInfo.BPhone1;
            }
            else
            {
                paramArray[0x2a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.BPhone2))
            {
                paramArray[0x2b].Value = salesOrderInfo.BPhone2;
            }
            else
            {
                paramArray[0x2b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.BFax1))
            {
                paramArray[0x2c].Value = salesOrderInfo.BFax1;
            }
            else
            {
                paramArray[0x2c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.BFax2))
            {
                paramArray[0x2d].Value = salesOrderInfo.BFax2;
            }
            else
            {
                paramArray[0x2d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.BVat))
            {
                paramArray[0x2e].Value = salesOrderInfo.BVat;
            }
            else
            {
                paramArray[0x2e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CarrierID))
            {
                paramArray[0x2f].Value = salesOrderInfo.CarrierID;
            }
            else
            {
                paramArray[0x2f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CarrierName))
            {
                paramArray[0x30].Value = salesOrderInfo.CarrierName;
            }
            else
            {
                paramArray[0x30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CarrierAddress))
            {
                paramArray[0x31].Value = salesOrderInfo.CarrierAddress;
            }
            else
            {
                paramArray[0x31].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CarrierCity))
            {
                paramArray[50].Value = salesOrderInfo.CarrierCity;
            }
            else
            {
                paramArray[50].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CarrierProv))
            {
                paramArray[0x33].Value = salesOrderInfo.CarrierProv;
            }
            else
            {
                paramArray[0x33].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CarrierZip))
            {
                paramArray[0x34].Value = salesOrderInfo.CarrierZip;
            }
            else
            {
                paramArray[0x34].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CarrierCountryCode))
            {
                paramArray[0x35].Value = salesOrderInfo.CarrierCountryCode;
            }
            else
            {
                paramArray[0x35].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CarrierCountry))
            {
                paramArray[0x36].Value = salesOrderInfo.CarrierCountry;
            }
            else
            {
                paramArray[0x36].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CarrierPhone))
            {
                paramArray[0x37].Value = salesOrderInfo.CarrierPhone;
            }
            else
            {
                paramArray[0x37].Value = DBNull.Value;
            }
            paramArray[0x38].Value = salesOrderInfo.DepartureDate;
            if (!string.IsNullOrEmpty(salesOrderInfo.TrailerNumber))
            {
                paramArray[0x39].Value = salesOrderInfo.TrailerNumber;
            }
            else
            {
                paramArray[0x39].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.TrailerOwner))
            {
                paramArray[0x3a].Value = salesOrderInfo.TrailerOwner;
            }
            else
            {
                paramArray[0x3a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.DriverName))
            {
                paramArray[0x3b].Value = salesOrderInfo.DriverName;
            }
            else
            {
                paramArray[0x3b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.ProNumber))
            {
                paramArray[60].Value = salesOrderInfo.ProNumber;
            }
            else
            {
                paramArray[60].Value = DBNull.Value;
            }
            paramArray[0x3d].Value = salesOrderInfo.Door;
            paramArray[0x3e].Value = salesOrderInfo.Route;
            paramArray[0x3f].Value = salesOrderInfo.Stop;
            if (!string.IsNullOrEmpty(salesOrderInfo.Stage))
            {
                paramArray[0x40].Value = salesOrderInfo.Stage;
            }
            else
            {
                paramArray[0x40].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.TransportationMode))
            {
                paramArray[0x41].Value = salesOrderInfo.TransportationMode;
            }
            else
            {
                paramArray[0x41].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.TransportationService))
            {
                paramArray[0x42].Value = salesOrderInfo.TransportationService;
            }
            else
            {
                paramArray[0x42].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.IncoTerms))
            {
                paramArray[0x43].Value = salesOrderInfo.IncoTerms;
            }
            else
            {
                paramArray[0x43].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.PaymentTerm))
            {
                paramArray[0x44].Value = salesOrderInfo.PaymentTerm;
            }
            else
            {
                paramArray[0x44].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.ContainerType))
            {
                paramArray[0x45].Value = salesOrderInfo.ContainerType;
            }
            else
            {
                paramArray[0x45].Value = DBNull.Value;
            }
            paramArray[70].Value = salesOrderInfo.ContainerQty;
            if (!string.IsNullOrEmpty(salesOrderInfo.LoadID))
            {
                paramArray[0x47].Value = salesOrderInfo.LoadID;
            }
            else
            {
                paramArray[0x47].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.ExternalLoadID))
            {
                paramArray[0x48].Value = salesOrderInfo.ExternalLoadID;
            }
            else
            {
                paramArray[0x48].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CountryOfOrigin))
            {
                paramArray[0x49].Value = salesOrderInfo.CountryOfOrigin;
            }
            else
            {
                paramArray[0x49].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.CountryDestination))
            {
                paramArray[0x4a].Value = salesOrderInfo.CountryDestination;
            }
            else
            {
                paramArray[0x4a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.DischargePlace))
            {
                paramArray[0x4b].Value = salesOrderInfo.DischargePlace;
            }
            else
            {
                paramArray[0x4b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.DeliveryPlace))
            {
                paramArray[0x4c].Value = salesOrderInfo.DeliveryPlace;
            }
            else
            {
                paramArray[0x4c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.Udf1))
            {
                paramArray[0x4d].Value = salesOrderInfo.Udf1;
            }
            else
            {
                paramArray[0x4d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.Udf2))
            {
                paramArray[0x4e].Value = salesOrderInfo.Udf2;
            }
            else
            {
                paramArray[0x4e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.Udf3))
            {
                paramArray[0x4f].Value = salesOrderInfo.Udf3;
            }
            else
            {
                paramArray[0x4f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.Udf4))
            {
                paramArray[80].Value = salesOrderInfo.Udf4;
            }
            else
            {
                paramArray[80].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.Udf5))
            {
                paramArray[0x51].Value = salesOrderInfo.Udf5;
            }
            else
            {
                paramArray[0x51].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.Udf6))
            {
                paramArray[0x52].Value = salesOrderInfo.Udf6;
            }
            else
            {
                paramArray[0x52].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.Udf7))
            {
                paramArray[0x53].Value = salesOrderInfo.Udf7;
            }
            else
            {
                paramArray[0x53].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.Udf8))
            {
                paramArray[0x54].Value = salesOrderInfo.Udf8;
            }
            else
            {
                paramArray[0x54].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.Udf9))
            {
                paramArray[0x55].Value = salesOrderInfo.Udf9;
            }
            else
            {
                paramArray[0x55].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.Udf10))
            {
                paramArray[0x56].Value = salesOrderInfo.Udf10;
            }
            else
            {
                paramArray[0x56].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.Remark))
            {
                paramArray[0x57].Value = salesOrderInfo.Remark;
            }
            else
            {
                paramArray[0x57].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(salesOrderInfo.Remark2))
            {
                paramArray[0x58].Value = salesOrderInfo.Remark2;
            }
            else
            {
                paramArray[0x58].Value = DBNull.Value;
            }
            paramArray[0x59].Value = salesOrderInfo.IsActive;
            paramArray[90].Value = salesOrderInfo.CreatedBy;
            paramArray[0x5b].Value = salesOrderInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateSalesOrder(SalesOrderInfo salesOrderInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                salesOrderInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_SalesOrder_Parameters(salesOrderInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_SALES_ORDER.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateSalesOrder(DataBase dataBase, DbTransaction tran, SalesOrderInfo salesOrderInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                salesOrderInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_SalesOrder_Parameters(salesOrderInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_SALES_ORDER.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public void UpdateSalesOrderStatus(string salesOrderID, string status, string updatedBy, string whLoginID)
        {
            string sql = this.SQL_UPDATE_SALES_ORDER_STATUS.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", salesOrderID), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy) };
            DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras);
        }

        public void UpdateSalesOrderStatus(DataBase dataBase, DbTransaction tran, string salesOrderID, string status, string updatedBy, string whLoginID)
        {
            string sql = this.SQL_UPDATE_SALES_ORDER_STATUS.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", salesOrderID), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy) };
            dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
        }

        public void UpdateSalesOrderSubmitted(string salesOrderID, string SubmittedBy, string whLoginID)
        {
            string sql = this.SQL_UPDATE_SALES_ORDER_SUBMITTEDBY.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", salesOrderID), new SqlParameter("@SUBMITTED_BY", SubmittedBy), new SqlParameter("@UPDATED_BY", SubmittedBy), new SqlParameter("@SUBMITTED_DATE", DateTime.Now) };
            DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras);
        }

        public void UpdateSalesOrderSubmitted(DataBase dataBase, DbTransaction tran, string salesOrderID, string SubmittedBy, string whLoginID)
        {
            string sql = this.SQL_UPDATE_SALES_ORDER_STATUS.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", salesOrderID), new SqlParameter("@SUBMITTED_BY", SubmittedBy), new SqlParameter("@UPDATED_BY", SubmittedBy) };
            dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
        }
    }
}

